package com.singfan.core.dao.database.impl;

import com.singfan.common.db.*;
import com.singfan.common.db.config.Table;
import com.singfan.core.dao.database.IOrderDao;
import com.singfan.core.dao.database.model.Orders;
import com.singfan.core.dao.database.model.constant.OrderStatusType;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;

/**
 *
 * Author: hansonghe
 * Date: 2015-08-19 12:12
 * Desc:
 */
@Repository
public class OrderDaoImpl extends DaoBaseGeneric<Orders> implements IOrderDao {
    @Override
    protected String getTableName(Object hashKey) {
        return tableNameBase;
    }

    @Override
    protected String getDatabaseId(Object hashKey) {
        return "app_1";
    }

    @Override
    public void create(Orders order) {
        Table table = DbContext.getDbConfigProvider().getTable(tableNameBase);
        int tableId = table.getId();
        int version = getTableNewestVersion();
        order.setId(PrimaryKeyBuilder.getInstance().build(tableId, version));
        insert(null, order);
    }

    @Override
    public void update(Orders order) {
        updateByPK(null, order);
    }

    @Override
    public void deleteById(long orderId) {
        deleteByPK(null, orderId);
    }

    @Override
    public void updateChannelSerialNumber(long orderId, String payChannelSerialNumber) {
        String sql = "UPDATE " + getTableName(null) + " SET pay_channel_serial_number=?, update_time=? WHERE id=?";
        execute(null, sql, payChannelSerialNumber, new Date(), orderId);
    }

    @Override
    public void updateStatus(long orderId, int status) {
        String sql = "UPDATE " + getTableName(null) + " SET status=?, update_time=? WHERE id=?";
        execute(null, sql, status, new Date(), orderId);
    }

    @Override
    public Orders selectOrder(String payChannelSerialNumber) {
        String sql = "SELECT * FROM " + getTableName(null) + " WHERE pay_channel_serial_number=?";
        return selectOne(null, sql, payChannelSerialNumber);
    }

    @Override
    public List<Orders> selectCustomerOrderList(long customerId, int orderStatus, int pageIndex, int pageSize) {
        List<Orders> result;

        if (OrderStatusType.isOrderStatusType(orderStatus)){
            if (orderStatus == OrderStatusType.SERVICED){
                String sql = "SELECT * FROM " + getTableName(null) + " WHERE customer_id=? AND status=? AND comment_star=0 ORDER BY create_time DESC limit ?,?";
                result = selectList(null, sql, customerId, OrderStatusType.SERVICED, pageIndex*pageSize, pageSize);
            }
            else {
                String sql = "SELECT * FROM " + getTableName(null) + " WHERE customer_id=? AND status=? ORDER BY create_time DESC limit ?,?";
                result = selectList(null, sql, customerId, orderStatus, pageIndex*pageSize, pageSize);
            }
        }
        else {
            String sql = "SELECT * FROM " + getTableName(null) + " WHERE customer_id=? ORDER BY create_time DESC limit ?,?";
            result = selectList(null, sql, customerId, pageIndex*pageSize, pageSize);
        }

        return result;
    }

    @Override
    public Orders selectOrder(long orderId) {
        String sql = "SELECT * FROM " + getTableName(null) + " WHERE id=?";
        return selectOne(null, sql, orderId);
    }

    @Override
    public void updateCommentStar(long orderId, int star) {
        String sql = "UPDATE " + getTableName(null) + " SET comment_star=? WHERE id=?";
        execute(null, sql, star, orderId);
    }

    @Override
    public int barberOrderCount(long barberId) {
        int result = 0;
        String sql = "SELECT COUNT(id) FROM " + getTableName(null) + " WHERE barber_id=? AND status>=?";
        Object count = executeScalar(null, sql, barberId, OrderStatusType.PAID);
        if (count != null){
            result = Integer.valueOf(count.toString());
        }
        return result;
    }

    @Override
    public int barberUnfinishOrderCount(long barberId) {
        int result = 0;
        String sql = "SELECT COUNT(id) FROM " + getTableName(null) + " WHERE barber_id=? AND status=?";
        Object count = executeScalar(null, sql, barberId, OrderStatusType.PAID);
        if (count != null){
            result = Integer.valueOf(count.toString());
        }
        return result;
    }

    @Override
    public List<Orders> selectBarberOrderList(long barberId, int orderStatus, int pageIndex, int pageSize) {
        List<Orders> result;

        if (OrderStatusType.isOrderStatusType(orderStatus)){
            String sql = "SELECT * FROM " + getTableName(null) + " WHERE barber_id=? AND status=? limit ?,?";
            result = selectList(null, sql, barberId, orderStatus, pageIndex*pageSize, pageSize);
        }
        else {
            String sql = "SELECT * FROM " + getTableName(null) + " WHERE barber_id=? limit ?,?";
            result = selectList(null, sql, barberId, pageIndex*pageSize, pageSize);
        }

        return result;
    }

    @Override
    public List<Long> selectExpiredOrder(Date limitCreateTime) {
        String sql = "SELECT id FROM " + getTableName(null) + " WHERE status=? AND create_time<?";
        List<Object> paramList = new ArrayList<>();
        paramList.add(OrderStatusType.UNPAID);
        paramList.add(limitCreateTime);
        return (List<Long>)executeQuery(null, sql, paramList, new LongResultSetReader());
    }

    @Override
    synchronized public boolean isExist(long barberId, int dayTime, int clockTime) {
        String sql = "SELECT id FROM " + getTableName(null) + " WHERE barber_id=? AND appointment_day=? AND appointment_clock=?";
        return super.isExist(null, sql, barberId, dayTime, clockTime);
    }

    @Override
    public LinkedHashMap<Integer, Integer> selectBarberIncomeTotalByMonth(long barberId) {
        String sql = "SELECT arrival_month, sum(original_price) as amount FROM " + getTableName(null) + " WHERE barber_id=? AND status = 4 GROUP BY arrival_month ORDER BY arrival_month DESC";
        List<Object> paramList = new ArrayList<>();
        paramList.add(barberId);
        return (LinkedHashMap<Integer, Integer>)executeQuery(null, sql, paramList, new IResultSetReader() {
            @Override
            public Object readObject(ResultSet resultSet) {
                LinkedHashMap<Integer, Integer> resultMap = new LinkedHashMap<Integer, Integer>();
                try {
                    while(resultSet.next()){
                        resultMap.put(resultSet.getInt("arrival_month"), resultSet.getInt("amount"));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return resultMap;
            }
        });
    }

    @Override
    public int selectBarberIncomeTotal(long barberId) {
        int result = 0;
        String sql = "SELECT sum(original_price) as amount FROM " + getTableName(null) + " WHERE barber_id=? AND status = 4";
        Object objResult = executeScalar(null, sql, barberId);
        if(objResult != null){
            result = Integer.parseInt(objResult.toString());
        }

        return result;
    }

    @Override
    public List<Orders> selectBarberOrderDetailList(long barberId, int orderFilterType, int pageIndex, int pageSize) {
        List<Orders> result;

        if (orderFilterType == 1){
            String sql = "SELECT * FROM " + getTableName(null) + " WHERE barber_id=? AND status=3 ORDER BY create_time DESC limit ?,?";
            result = selectList(null, sql, barberId, pageIndex*pageSize, pageSize);
        }
        else if (orderFilterType == 2){
            String sql = "SELECT * FROM " + getTableName(null) + " WHERE barber_id=? AND status in (5,6) ORDER BY create_time DESC limit ?,?";
            result = selectList(null, sql, barberId, pageIndex*pageSize, pageSize);
        }
        else if (orderFilterType == 3){
            String sql = "SELECT * FROM " + getTableName(null) + " WHERE barber_id=? AND status in (7,8) ORDER BY create_time DESC limit ?,?";
            result = selectList(null, sql, barberId, pageIndex*pageSize, pageSize);
        }
        else {
            String sql = "SELECT * FROM " + getTableName(null) + " WHERE barber_id=? AND status >= 3 ORDER BY create_time DESC limit ?,?";
            result = selectList(null, sql, barberId, pageIndex*pageSize, pageSize);
        }

        return result;
    }

    @Override
    public void updateOrderServiced(long orderId) {
        Date now = new Date();
        String sql = "UPDATE " + getTableName(null) + " SET status=?, arrival_time=?, arrival_month=?, update_time=? WHERE id=?";
        execute(null, sql, OrderStatusType.SERVICED, now, getYearAndMonth(), now, orderId);
    }

    @Override
    public int selectShopOneDayIncome(long shopId, int dayTime) {
        int result = 0;
        String sql = "SELECT sum(original_price) as amount FROM " + getTableName(null) + " WHERE shop_id=? AND appointment_day=? AND status = 4";
        Object objResult = executeScalar(null, sql, shopId, dayTime);
        if(objResult != null){
            result = Integer.parseInt(objResult.toString());
        }

        return result;
    }

    @Override
    public int selectShopOneDayOrderCount(long shopId, int dayTime) {
        int result = 0;
        String sql = "SELECT count(id) as amount FROM " + getTableName(null) + " WHERE shop_id=? AND appointment_day=? AND status = 4";
        Object objResult = executeScalar(null, sql, shopId, dayTime);
        if(objResult != null){
            result = Integer.parseInt(objResult.toString());
        }

        return result;
    }

    @Override
    public List<Orders> selectShopOneDayOrderList(long shopId, int dayTime) {
        String sql = "SELECT * FROM " + getTableName(null) + " WHERE shop_id=? AND appointment_day=? AND status = 4";
        return selectList(null, sql, shopId, dayTime);
    }

    @Override
    public int selectShopOneMonthIncome(long shopId, int monthTime) {
        int result = 0;
        String sql = "SELECT sum(original_price) as amount FROM " + getTableName(null) + " WHERE shop_id=? AND arrival_month=? AND status = 4";
        Object objResult = executeScalar(null, sql, shopId, monthTime);
        if(objResult != null){
            result = Integer.parseInt(objResult.toString());
        }

        return result;
    }

    @Override
    public int selectShopOneMonthOrderCount(long shopId, int monthTime) {
        int result = 0;
        String sql = "SELECT count(original_price) as amount FROM " + getTableName(null) + " WHERE shop_id=? AND arrival_month=? AND status = 4";
        Object objResult = executeScalar(null, sql, shopId, monthTime);
        if(objResult != null){
            result = Integer.parseInt(objResult.toString());
        }

        return result;
    }

    @Override
    public Orders selectOrdersBySecurityCode(long shopId, String securityCode) {
        String sql = "SELECT * FROM " + getTableName(null) + " WHERE shop_id=? AND reservation_code=?";
        return selectOne(null, sql, shopId, securityCode);
    }

    @Override
    public List<Orders> selectShopOrderDetailList(long shopId, int orderFilterType, int pageIndex, int pageSize) {
        String sql;
        List<Orders> result;

        if (orderFilterType == 1){
            sql = "SELECT * FROM " + getTableName(null) + " WHERE shop_id=? AND status=3 ORDER BY create_time DESC limit ?,?";
        }
        else if (orderFilterType == 2){
            sql = "SELECT * FROM " + getTableName(null) + " WHERE shop_id=? AND status in (5,6) ORDER BY create_time DESC limit ?,?";
        }
        else if (orderFilterType == 3){
            sql = "SELECT * FROM " + getTableName(null) + " WHERE shop_id=? AND status in (7,8) ORDER BY create_time DESC limit ?,?";
        }
        else {
            sql = "SELECT * FROM " + getTableName(null) + " WHERE shop_id=? AND status >= 3 ORDER BY create_time DESC limit ?,?";
        }
        result = selectList(null, sql, shopId, pageIndex*pageSize, pageSize);

        return result;
    }

    @Override
    public List<Orders> selectCustomerOrderList(String customerPhone) {
        String sql = "SELECT * FROM " + getTableName(null) + " WHERE customer_phone_number=? ORDER BY create_time DESC";
        return selectList(null, sql, customerPhone);
    }

    @Override
    public List<Orders> selectOneMonthOrdersList(int month, long shopId) {
        List<Orders> result;

        if (shopId > 0){
            String sql = "SELECT * FROM " + getTableName(null) + " WHERE arrival_month=? AND shop_id=?";
            result = selectList(null, sql, month, shopId);
        }
        else {
            String sql = "SELECT * FROM " + getTableName(null) + " WHERE arrival_month=?";
            result = selectList(null, sql, month);
        }

        return result;
    }

    private static int getYearAndMonth(){
        Date now = new Date();
        int year = now.getYear() + 1900;
        int month = now.getMonth() + 1;
        String monthText = fill0(month);

        return Integer.parseInt(String.format("%1$d%2$s", year, monthText));
    }

    private static String fill0(int value){
        String result;

        if (value < 10){
            result = "0" + value;
        }
        else{
            result = String.valueOf(value);
        }

        return result;
    }
}
